package dao.book;

import dao.DaoException;
import model.Book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class BookDaoImp implements BookDao {
    @Override
    public boolean addBook(Book book) throws DaoException {
        String sql = "INSERT INTO book VALUES(?,?,?,?,?,?)";
        try (
                Connection conn = getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ) {
            pstmt.setInt(1, book.getId());
            pstmt.setString(2, book.getName());
            pstmt.setFloat(3, book.getPrice());
            pstmt.setString(4, book.getWriter());
            pstmt.setString(5, book.getClassify());
            pstmt.setString(6, book.getState());
            pstmt.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public ArrayList<Book> findBooksByPageAll(int pageNum, int pageSize) throws DaoException {
        int start = (pageNum - 1) * pageSize;
        ArrayList<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book limit ?,?";
        try (Connection conn = getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ) {
            preparedStatement.setInt(1, start);
            preparedStatement.setInt(2, pageSize);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Book book = new Book();

                book.setId(resultSet.getInt("id"));
                book.setName(resultSet.getString("name"));
                book.setPrice(resultSet.getFloat("price"));
                book.setWriter(resultSet.getString("writer"));
                book.setClassify(resultSet.getString("classify"));
                book.setState(resultSet.getString("state"));
                books.add(book);
            }
            return books;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public int findBookNumAll() throws DaoException {
        String sql = "SELECT COUNT(*) as total FROM `book`";

        try (Connection conn = getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ) {
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public ArrayList<Book> searchBooksByPageAll(String pattern, int pageNum, int pageSize) throws DaoException {
        pattern = "%" + pattern + "%";
        int start = (pageNum - 1) * pageSize;
        ArrayList<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book WHERE `name` LIKE ? limit ?,?";
        try (Connection conn = getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ) {
            preparedStatement.setString(1, pattern);
            preparedStatement.setInt(2, start);
            preparedStatement.setInt(3, pageSize);

            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Book book = new Book();

                book.setId(resultSet.getInt("id"));
                book.setName(resultSet.getString("name"));
                book.setPrice(resultSet.getFloat("price"));
                book.setWriter(resultSet.getString("writer"));
                book.setClassify(resultSet.getString("classify"));
                book.setState(resultSet.getString("state"));
                books.add(book);
            }
            return books;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public int searchBookNumAll(String pattern) throws DaoException {
        pattern = "%" + pattern + "%";
        String sql = "SELECT COUNT(*) as total FROM `book` WHERE `name` LIKE ?";

        try (Connection conn = getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ) {
            preparedStatement.setString(1, pattern);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public ArrayList<Book> findBooksByPageBorrow(int pageNum, int pageSize) throws DaoException {

        int start = (pageNum - 1) * pageSize;
        ArrayList<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book WHERE state = '未借' limit ?,?";
        try (Connection conn = getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ) {
            preparedStatement.setInt(1, start);
            preparedStatement.setInt(2, pageSize);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Book book = new Book();

                book.setId(resultSet.getInt("id"));
                book.setName(resultSet.getString("name"));
                book.setPrice(resultSet.getFloat("price"));
                book.setWriter(resultSet.getString("writer"));
                book.setClassify(resultSet.getString("classify"));
                book.setState(resultSet.getString("state"));
                books.add(book);
            }
            return books;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public int findBookNumBorrow() throws DaoException {
        String sql = "SELECT COUNT(*) as total FROM `book` WHERE state = '未借'";

        try (Connection conn = getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ) {
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public ArrayList<Book> searchBooksByPageBorrow(String pattern, int pageNum, int pageSize) throws DaoException {
        pattern = "%" + pattern + "%";
        int start = (pageNum - 1) * pageSize;
        ArrayList<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book WHERE state = '未借' AND `name` LIKE ? limit ?,?";
        try (Connection conn = getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ) {
            preparedStatement.setString(1, pattern);
            preparedStatement.setInt(2, start);
            preparedStatement.setInt(3, pageSize);

            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Book book = new Book();

                book.setId(resultSet.getInt("id"));
                book.setName(resultSet.getString("name"));
                book.setPrice(resultSet.getFloat("price"));
                book.setWriter(resultSet.getString("writer"));
                book.setClassify(resultSet.getString("classify"));
                book.setState(resultSet.getString("state"));
                books.add(book);
            }
            return books;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public int searchBookNumBorrow(String pattern) throws DaoException {
        pattern = "%" + pattern + "%";
        String sql = "SELECT COUNT(*) as total FROM `book` WHERE state = '未借' AND `name` LIKE ?";

        try (Connection conn = getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ) {
            preparedStatement.setString(1, pattern);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public void deleteBooksById(int id) throws DaoException {
        String sql = "DELETE FROM book WHERE id=?";
        try (
                Connection conn = getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ) {
            pstmt.setInt(1, id);
            pstmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateBorrowBook(int id, String state) throws DaoException {
        String sql = "UPDATE book SET state = '" + state + "' WHERE id = ?";

        boolean success = false;
        try (Connection conn = getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql)
        ) {
            preparedStatement.setInt(1, id);
            success = !preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
